package com.pym.util;


import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

public class Common_ExcelUtil {
    private final static Logger logger = LoggerFactory.getLogger(Common_ExcelUtil.class);


    public static void downexcle(HttpServletResponse response, List<?> projects,
                                 String[] columnNames, String[] keys, String filename)
            throws IOException {

        List<Map<String, Object>> list = createExcelRecord(projects);

        ByteArrayOutputStream os = new ByteArrayOutputStream();

        createWorkBook(list, keys, columnNames).write(os);


        ExcelDownloadUtil.downloadExcel(response, os, filename);

    }

    public static List<Map<String, Object>> createExcelRecord(List<?> list) {

        List<Map<String, Object>> listmap = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();
        map.put("sheetName", "sheet1");
        listmap.add(map);
        Object project;
        if (!CollectionUtils.isEmpty(list)) {
            for (int j = 0; j < list.size(); j++) {
                project = list.get(j);
                Map<String, Object> mapValue;
                try {
                    mapValue = ReflexUtil.PO2Map(project);
                    listmap.add(mapValue);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return listmap;
    }

    public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) {

        // 创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth((short) i, (short) (40 * 150));
        }

        // 创建两种单元格格式
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 15);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        f.setFontName("黑体");

        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());
        f2.setFontName("Arial");

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        // 设置单元格内字体左右居中
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置单元格内字体上下居中
        cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 设置自动换行
        cs.setWrapText(true);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        // 设置单元格内字体左右居中
        cs2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置单元格内字体上下居中
        cs2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 设置自动换行
        cs2.setWrapText(true);


        // 创建第一行
        Row row = sheet.createRow((short) 0);
        row.setHeightInPoints(28);
        // 设置列名
        for (int i = 0; i < columnNames.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        // 设置每行每列的值
        if (!CollectionUtils.isEmpty(list)) {
            for (short i = 1; i < list.size(); i++) {
                // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
                // 创建一行，在页sheet上
                Row row1 = sheet.createRow((short) i);
                row1.setHeightInPoints(28);
                // 在row行上创建一个方格
                for (short j = 0; j < keys.length; j++) {
                    Cell cell = row1.createCell(j);
                    // 判断是否为时间格式，如果为时间格式，要进行格式转换
                    String valueString = "";
                    Drawing drawing = sheet.createDrawingPatriarch();
                    // 判断单元格为显示图片字段时
                    if(keys[j] == "pitcureUrl" && StringUtils.hasLength(list.get(i).get(keys[j]).toString())) {
                        String pitcureUrl = list.get(i).get(keys[j]).toString();
                        File file = new File(pitcureUrl);
                        if (file.exists()) {
                            try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                                 FileInputStream inputStream = new FileInputStream(file)) {
                                IOUtils.copy(inputStream, outputStream);
                                XSSFClientAnchor anchor = new XSSFClientAnchor(30, 15, 0, 0, (short) (2), j, (short) (3), j + 1);
                                drawing.createPicture(anchor, wb.addPicture(outputStream.toByteArray(), SXSSFWorkbook.PICTURE_TYPE_PNG));
                                outputStream.flush();
                            } catch (IOException e) {
                                logger.error("截图写入失败;截图地址：filePath；",pitcureUrl);
                            }
                        }
                    } else {
                        Object bufferValue = list.get(i).get(keys[j]);
                        if (bufferValue instanceof Date) {
                            valueString = TimeTools.parseData((Date) bufferValue);
                        } else {
                            valueString = bufferValue == null ? "" : bufferValue.toString();
                        }
                    }
                    cell.setCellValue(valueString);
                    cell.setCellStyle(cs2);
                }
            }
        }
        return wb;
    }


}
